﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExportProcSql
{
    public partial class MainFrm : Form
    {
        private string ConnstrSqlServer = "";
        public MainFrm()
        {
            InitializeComponent();
            SetMsg("");
        }

        private bool isRun = false;

        private void SetMsg(string msg)
        {
            if (labMsg.InvokeRequired)
            {
                labMsg.Invoke(new Action<string>(p =>
                {
                    labMsg.Text = p;
                }), msg);
            }
            else
                labMsg.Text = msg;
        }


        private void SetConnstr()
        {
            if (string.IsNullOrWhiteSpace(txtAddr.Text.Trim())
                || string.IsNullOrWhiteSpace(txtDBName.Text.Trim())
                || string.IsNullOrWhiteSpace(txtPassword.Text.Trim())
                || string.IsNullOrWhiteSpace(txtUser.Text.Trim()))
            {
                SetMsg("信息不可以为空");
            }
            ConnstrSqlServer = $"server={txtAddr.Text.Trim()};uid={txtUser.Text.Trim()};pwd={txtPassword.Text.Trim()};database={txtDBName.Text.Trim()}";
            SqlHelper.connStr = ConnstrSqlServer;
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            labLink.Text = "";
            SetMsg("");
            SetConnstr();
            if (SqlHelper.test())
            {
                labLink.Text = "连接成功";
            }
            else
            {
                labLink.Text = "连接失败";
            }
        }

        /// <summary>
        /// 全部导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, EventArgs e)
        {
            if (isRun)
                return;
            isRun = true;
            SetConnstr();
            if (!SqlHelper.test())
            {
                SetMsg("数据库连接失败");
                return;
            }
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "脚本文件夹");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);


            Task.Run(() =>
            {
                ExprotTable(path).Wait();

                ExprotProc(path).Wait();

                ExprotView(path).Wait();
                ExprotTirgger(path).Wait();

                ExprotFunc(path).Wait();
                SetMsg($"导出完成");
                isRun = false;
            });


        }

        #region 导出建表语句


        private Task ExprotTable(string path)
        {
            return Task.Run(() =>
             {
                 path = Path.Combine(path, "1.Table");
                 if (!Directory.Exists(path))
                     Directory.CreateDirectory(path);
                 var list = GetALLTable();
                 SetMsg($"总共查询出【{list.Count}】个表");
                 ExecHelpTableSql();
                 int i = 1;
                 foreach (var item in list)
                 {
                     SetMsg($"{i}/{list.Count}   正在导出表【{item}】。");
                     SaveTableText(path, item, i);
                     i++;
                 }


                 SetMsg($"导出表完成【{list.Count}】");

             });

        }

        /// <summary>
        /// 创建导出表语句脚本
        /// </summary>
        private void ExecHelpTableSql()
        {
            string sql = null;
            Assembly assm = Assembly.GetExecutingAssembly();
            using (Stream str = assm.GetManifestResourceStream("ExportProcSql.Resources.sp_helptable.sql"))
            {
                using (StreamReader sw = new StreamReader(str, Encoding.UTF8))
                {
                    sql = sw.ReadToEnd();
                }
            }

            //using (StreamReader sw = new StreamReader(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sp_helptable.sql"), Encoding.UTF8))
            //{
            //    sql = sw.ReadToEnd();
            //}
            string[] sArray = Regex.Split(sql, "go", RegexOptions.IgnoreCase);
            foreach (var item in sArray)
            {
                SqlHelper.ExecuteNonQuery(item);
            }

        }

        /// <summary>
        /// 保存建表语句文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="procName"></param>
        /// <param name="index"></param>
        private void SaveTableText(string path, string tableName, int index)
        {
            string sql = "exec sp_helptable @tableName";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@tableName",tableName)
            });

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.AppendLine(dt.Rows[i][0].ToString());
            }
            path = Path.Combine(path, $"{ tableName}.sql");
            using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
            {

                sw.WriteLine($@"IF Not EXISTS(SELECT * FROM sys.tables WHERE name = '{tableName}') 
BEGIN
{sb}
END
GO");
                sw.Flush();
            }
        }




        /// <summary>
        /// 获取所有表名
        /// </summary>
        /// <returns></returns>
        private List<string> GetALLTable()
        {
            string sql = "SELECT* FROM sys.tables WHERE name LIKE @like";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@like",txtLike.Text.Trim())
            });

            List<string> list = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i]["name"].ToString());
            }
            return list;
        }

        #endregion
        #region 导出视图

        private Task ExprotView(string path)
        {
            return Task.Run(() =>
             {
                 path = Path.Combine(path, "3.View");
                 if (!Directory.Exists(path))
                     Directory.CreateDirectory(path);
                 var list = GetALLView();
                 SetMsg($"总共查询出【{list.Count}】个视图");
                 int i = 1;
                 foreach (var item in list)
                 {
                     SetMsg($"{i}/{list.Count}   正在导出视图【{item}】。");
                     SaveViewText(path, item, i);
                     i++;
                 }

                 SetMsg($"导出视图完成【{list.Count}】");
             });
        }

        /// <summary>
        /// 保存视图文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="procName"></param>
        /// <param name="index"></param>
        private void SaveViewText(string path, string viewName, int index)
        {
            string sql = "sp_helptext @viewName";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@viewName",viewName)
            });

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append(dt.Rows[i][0]);
            }
            path = Path.Combine(path, $"{ viewName}.sql");
            using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
            {

                sw.WriteLine($@"IF EXISTS(SELECT * FROM sys.views WHERE name = '{viewName}') 
BEGIN
    DROP VIEW {viewName}
END
GO
");
                sw.Write(sb);
                sw.Flush();
            }
        }


        /// <summary>
        /// 获取所有视图
        /// </summary>
        /// <returns></returns>
        private List<string> GetALLView()
        {
            string sql = "SELECT * FROM sys.views WHERE name LIKE @like";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@like",txtLike.Text.Trim())
            });

            List<string> list = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i]["name"].ToString());
            }
            return list;
        }
        #endregion
        #region 导出存储

        private Task ExprotProc(string path)
        {
            return Task.Run(() =>
             {
                 path = Path.Combine(path, "6.Proc");
                 if (!Directory.Exists(path))
                     Directory.CreateDirectory(path);
                 var list = GetALLProc();
                 SetMsg($"总共查询出【{list.Count}】个存储");
                 int i = 1;
                 foreach (var item in list)
                 {
                     SetMsg($"{i}/{list.Count}   正在导出存储【{item}】。");
                     SaveProcText(path, item, i);
                     i++;
                 }

                 SetMsg($"导出存储完成【{list.Count}】");
             });
        }

        /// <summary>
        /// 保存存储文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="procName"></param>
        /// <param name="index"></param>
        private void SaveProcText(string path, string procName, int index)
        {
            string sql = "sp_helptext @procName";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@procName",procName)
            });

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append(dt.Rows[i][0]);
            }
            path = Path.Combine(path, $"{ procName}.sql");
            using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
            {

                sw.WriteLine($@"IF EXISTS(SELECT * FROM sys.procedures WHERE name = '{procName}') 
BEGIN
    DROP PROC {procName}
END
GO
");
                sw.Write(sb);
                sw.Flush();
            }
        }


        /// <summary>
        /// 获取所有存储
        /// </summary>
        /// <returns></returns>
        private List<string> GetALLProc()
        {
            string sql = "SELECT* FROM sys.procedures WHERE name LIKE @like";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@like",txtLike.Text.Trim())
            });

            List<string> list = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i]["name"].ToString());
            }
            return list;
        }
        #endregion
        #region 导出触发器

        private Task ExprotTirgger(string path)
        {
            return Task.Run(() =>
             {
                 path = Path.Combine(path, "5.Tirgger");
                 if (!Directory.Exists(path))
                     Directory.CreateDirectory(path);
                 var list = GetALLTirgger();
                 SetMsg($"总共查询出【{list.Count}】个触发器");
                 int i = 1;
                 foreach (var item in list)
                 {
                     SetMsg($"{i}/{list.Count}   正在导出触发器【{item}】。");
                     SaveTirggerText(path, item, i);
                     i++;
                 }

                 SetMsg($"导出触发器完成【{list.Count}】");
             });
        }

        /// <summary>
        /// 保存视图文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="procName"></param>
        /// <param name="index"></param>
        private void SaveTirggerText(string path, string triggersName, int index)
        {
            string sql = "sp_helptext @triggersName";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@triggersName",triggersName)
            });

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append(dt.Rows[i][0]);
            }
            path = Path.Combine(path, $"{triggersName}.sql");
            using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
            {

                sw.WriteLine($@"IF EXISTS(SELECT * FROM sys.triggers WHERE name = '{triggersName}') 
BEGIN
    DROP TRIGGER {triggersName}
END
GO
");
                sw.Write(sb);
                sw.Flush();
            }
        }


        /// <summary>
        /// 获取所有视图
        /// </summary>
        /// <returns></returns>
        private List<string> GetALLTirgger()
        {
            string sql = "SELECT * FROM sys.triggers WHERE name LIKE @like";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@like",txtLike.Text.Trim())
            });

            List<string> list = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i]["name"].ToString());
            }
            return list;
        }
        #endregion

        #region 导出函数

        private Task ExprotFunc(string path)
        {
            return Task.Run(() =>
            {
                path = Path.Combine(path, "4.Function");
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);
                var list = GetALLFunc();
                SetMsg($"总共查询出【{list.Count}】个函数");
                int i = 1;
                foreach (var item in list)
                {
                    SetMsg($"{i}/{list.Count}   正在导出函数【{item}】。");
                    SaveFuncText(path, item, i);
                    i++;
                }

                SetMsg($"导出函数完成【{list.Count}】");
            });
        }

        /// <summary>
        /// 保存函数文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="procName"></param>
        /// <param name="index"></param>
        private void SaveFuncText(string path, string funcName, int index)
        {
            string sql = "sp_helptext @funcName";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@funcName",funcName)
            });

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append(dt.Rows[i][0]);
            }
            path = Path.Combine(path, $"{funcName}.sql");
            using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
            {

                sw.WriteLine($@"IF EXISTS(SELECT * FROM  sys.objects WHERE ( type='TF' OR type='FN' OR type='IF' OR type='AF' )and name = '{funcName}') 
BEGIN
    DROP FUNCTION {funcName}
END
GO
");
                sw.Write(sb);
                sw.Flush();
            }
        }


        /// <summary>
        /// 获取所有函数
        /// </summary>
        /// <returns></returns>
        private List<string> GetALLFunc()
        {
            string sql = "SELECT * FROM  sys.objects WHERE ( type='TF' OR type='FN' OR type='IF' OR type='AF')and name LIKE @like";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, new System.Data.SqlClient.SqlParameter[] {
                new System.Data.SqlClient.SqlParameter("@like",txtLike.Text.Trim())
            });

            List<string> list = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i]["name"].ToString());
            }
            return list;
        }
        #endregion

        /// <summary>
        /// 表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            if (isRun)
                return;
            isRun = true;
            SetConnstr();
            if (!SqlHelper.test())
            {
                SetMsg("数据库连接失败");
                return;
            }
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "脚本文件夹");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);


            Task.Run(() =>
            {
                ExprotTable(path).Wait();
                isRun = false;
            });
        }

        /// <summary>
        /// 视图
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            if (isRun)
                return;
            isRun = true;
            SetConnstr();
            if (!SqlHelper.test())
            {
                SetMsg("数据库连接失败");
                return;
            }
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "脚本文件夹");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);


            Task.Run(() =>
            {
                ExprotView(path).Wait();
                isRun = false;
            });
        }

        /// <summary>
        /// 存储
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            if (isRun)
                return;
            isRun = true;
            SetConnstr();
            if (!SqlHelper.test())
            {
                SetMsg("数据库连接失败");
                return;
            }
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "脚本文件夹");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);


            Task.Run(() =>
            {
                ExprotProc(path).Wait();
                isRun = false;
            });
        }

        /// <summary>
        /// 触发器
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {

            if (isRun)
                return;
            isRun = true;
            SetConnstr();
            if (!SqlHelper.test())
            {
                SetMsg("数据库连接失败");
                return;
            }
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "脚本文件夹");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            Task.Run(() =>
            {
                ExprotTirgger(path).Wait();
                isRun = false;
            });
        }

        /// <summary>
        /// 函数
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button5_Click(object sender, EventArgs e)
        {


            if (isRun)
                return;
            isRun = true;
            SetConnstr();
            if (!SqlHelper.test())
            {
                SetMsg("数据库连接失败");
                return;
            }
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "脚本文件夹");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            Task.Run(() =>
            {
                ExprotFunc(path).Wait();
                isRun = false;
            });
        }
    }
}
